create procedure [dbo].[BAEFindProductsWithCategory] as
SELECT DISTINCT OrderProduct.OrderProductID, p.TITLE COLLATE database_default AS Title, (CAST(p.WEB_DESC AS varchar(4096))) Description, IsSuperProduct, ProductCode, p.WEB_OPTION AS SellOnWeb, p.IS_KIT AS IsKit,
        (SELECT    TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = OrderProduct.OrderProductID) AS OrderCategoryID
    FROM     OrderProduct INNER JOIN Product p
        ON p.PRODUCT_CODE COLLATE database_default = OrderProduct.ProductCode COLLATE database_default
    WHERE     ((IsSuperProduct = 0 AND p.WEB_OPTION > 0))  AND (SELECT TOP 1 OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = OrderProduct.OrderProductID) IS NOT NULL--OR OrderProduct.OrderProductID NOT IN (SELECT OrderSuperProductChildProductID FROM OrderSuperProductChildProduct)
    UNION
    SELECT DISTINCT op.OrderProductID, op.Title  COLLATE database_default AS Title, op.Description  COLLATE database_default, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, CAST('0' AS bit)  AS IsKit,
        (SELECT TOP 1  OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = op.OrderProductID) AS OrderCategoryID
    FROM     OrderProduct op
    WHERE     IsSuperProduct = 1 AND op.SellOnWeb > 0 AND (SELECT TOP 1  OrderCategoryID FROM OrderProductCategoryLookup cat WHERE cat.OrderProductID = op.OrderProductID) IS NOT NULL

    ORDER BY Title
/*    SELECT DISTINCT OrderProduct.OrderProductID, p.Title AS Title, (CAST(p.Web_desc AS varchar(4096))) DESCRIPTION, isSuperProduct, ProductCode, p.Web_Option AS SellOnWeb,
        p.is_kit AS IsKit, (SELECT top 1 ordercategoryid FROM orderproductcategorylookup cat WHERE cat.orderproductid=OrderProduct.Orderproductid )  AS ordercategoryID
    FROM OrderProduct
    INNER JOIN Product p ON p.Product_Code = OrderProduct.ProductCode
    WHERE isSuperProduct = 0 AND OrderProduct.OrderProductID NOT IN (SELECT OrderSuperProductChildProductID FROM OrderSuperProductChildProduct)
    UNION
    SELECT DISTINCT op.OrderProductID, op.Title AS Title, op.DESCRIPTION, op.isSuperProduct, op.ProductCode, op.SellOnWeb, Cast('0' AS bit)  AS IsKit,
    (SELECT top 1 ordercategoryid FROM orderproductcategorylookup cat WHERE cat.orderproductid=op.Orderproductid )  AS ordercategoryID
    FROM OrderProduct op
    WHERE isSuperProduct = 1
    ORDER BY title
*/

GO